package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;
import db.DbConnectionPool;
import db.DbException;
import forms.formRegist;

public class registerModel {
	public static void registStudent(formRegist student)
	throws DbException {
		try {
			Connection conn = DbConnectionPool.getConnection();
			Statement stmt = conn.createStatement();
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO users (user_name,password,email) VALUES (?,md5(?),md5(?))");
			pstmt.setString(1,student.getLoginName());
			pstmt.setString(2,student.getPw1());
			if(student.getEmail().equals(""))
			pstmt.setString(3,null);
			else
			pstmt.setString(3,student.getEmail());
			pstmt.executeUpdate();
			pstmt = conn.prepareStatement("INSERT INTO user_roles(user_ref,user_name,role) VALUES (?,?,?)");
			ResultSet rs = stmt.executeQuery("SELECT user_id from users WHERE user_name ='"+student.getLoginName()+"'");
			rs.next();
			pstmt.setInt(1,rs.getInt(1));
			pstmt.setString(2,student.getLoginName());
			pstmt.setString(3,"student");
			pstmt.executeUpdate();
			pstmt = conn.prepareStatement("INSERT INTO user_status (user_id,user_name,user_status) VALUES (?,?,?)");
			rs = stmt.executeQuery("SELECT user_id from users WHERE user_name ='"+student.getLoginName()+"'");
			rs.next();
			pstmt.setInt(1,rs.getInt(1));
			pstmt.setString(2, student.getLoginName());
			pstmt.setBoolean(3, false);
			pstmt.executeUpdate();
			// Commit transaction
			conn.commit();
			pstmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}
}
